<?php
class sis_anggota_kartu_model extends MY_Model {
	var $model = 'sis_anggota_kartu';
	
	function GetJSON()
	{
		$ls_sql	= "	SELECT	A.ID_KARTU, A.ID_ANGGOTA, A.KD_JENIS_KARTU, A.NO_KARTU, A.PIN, A.ID_NUM, 
							NVL (A.ST_PRINT, 'N') ST_PRINT, TO_CHAR (A.TGL_PRINT, 'DD/MM/YYYY') TGL_PRINT, 
							NVL (A.ST_AKTIF, 'N') ST_AKTIF, TO_CHAR (A.TGL_AKTIF, 'DD/MM/YYYY') TGL_AKTIF, TO_CHAR (A.TGL_EXPIRE, 'DD/MM/YYYY') TGL_EXPIRE, 
							NVL (A.ST_AMBIL, 'N') ST_AMBIL, TO_CHAR (A.TGL_AMBIL, 'DD/MM/YYYY') TGL_AMBIL,
							NVL (A.ST_NONAKTIF, 'N') ST_NONAKTIF, TO_CHAR (A.TGL_NONAKTIF, 'DD/MM/YYYY') TGL_NONAKTIF, A.ID_DOK,
							A.KETERANGAN, B.NAMA_JENIS_KARTU 
					FROM	SIS_ANGGOTA_KARTU A 
							JOIN SIS_SETUP_JENIS_KARTU B ON A.KD_JENIS_KARTU = B.KD_JENIS_KARTU 
					WHERE	A.ID_ANGGOTA = '".post('id_anggota')."' 
					ORDER BY ".(post('sort')=='' ? ' ST_NONAKTIF ASC, A.TGL_AKTIF ' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDetailByID($as_id_anggota)
	{
		$ls_sql	= "	SELECT	A.ID_KARTU, A.ID_ANGGOTA, A.KD_JENIS_KARTU, A.NO_KARTU, A.PIN, A.ID_NUM, 
							NVL (A.ST_PRINT, 'N') ST_PRINT, TO_CHAR (A.TGL_PRINT, 'DD/MM/YYYY') TGL_PRINT,
							NVL (A.ST_AKTIF, 'N') ST_AKTIF, TO_CHAR (A.TGL_AKTIF, 'DD/MM/YYYY') TGL_AKTIF, TO_CHAR (A.TGL_EXPIRE, 'DD/MM/YYYY') TGL_EXPIRE,
							NVL (A.ST_AMBIL, 'N') ST_AMBIL, TO_CHAR (A.TGL_AMBIL, 'DD/MM/YYYY') TGL_AMBIL,
							NVL (A.ST_NONAKTIF, 'N') ST_NONAKTIF, TO_CHAR (A.TGL_NONAKTIF, 'DD/MM/YYYY') TGL_NONAKTIF, A.ID_DOK,
							A.KETERANGAN, B.NAMA_JENIS_KARTU
					FROM	SIS_ANGGOTA_KARTU A
							JOIN SIS_SETUP_JENIS_KARTU B ON A.KD_JENIS_KARTU = B.KD_JENIS_KARTU
					WHERE	A.ID_ANGGOTA = '".$as_id_anggota."'
					ORDER BY ST_NONAKTIF ASC, A.TGL_AKTIF ASC";
		
		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}
	
	function Add($arr_data){
		$this->db->insert('sis_anggota_kartu', $arr_data);
		
		$this->AuditTrail(	'sis_anggota_kartu',	// Table
							'Create',			// Action
							''					// Primary Key
							);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('sis_anggota_kartu', $arr_data, $arr_rule);
		
		$this->AuditTrail(	'sis_anggota_kartu',		// Table
							'Update',				// Action
							json_encode($arr_rule)	// Primary Key
							);
	}
	
	function GetSingle($as_idkartu){
		$result = $this->db->get_where('sis_anggota_kartu', array('id_kartu' => $as_idkartu));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_idkartu)
	{
		$result = $this->db->query("SELECT	A.ID_ANGGOTA, A.ST_AKTIF, 
							(CASE NVL(A.ST_AKTIF,'0') WHEN '0' THEN 'Daftar' WHEN '1' THEN 'Aktif' ELSE 'Non Aktif' END) NAMA_STATUS,
							A.KD_BANK, F.NAMA_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							B.PERSON_ID, B.ID_KARYAWAN_PK, B.GENDER_ID, B.ID_AGAMA_PK, B.ID_JENJANG_PK, B.KD_KANTOR, B.FULL_NAME, B.MOTHER_NAME,
							B.IDENTITY_TYPE_CODE, B.IDENTITY_NUMBER, B.NIP, B.BIRTH_PLACE, TO_CHAR(B.BIRTH_DATE,'DD/MM/YYYY') BIRTH_DATE, B.EMAIL, B.PHONE, B.USER_ID, 
							C.KD_JENIS_ANGGOTA, C.NAMA_JENIS_ANGGOTA, 
							D.ADDRESS_TYPE_CODE, D.ADDRESS, D.RT, D.RW, D.VILLAGE, D.SUBDISTRICT, 
							D.CITY_CODE, D.PROVINCE_CODE, D.COUNTRY_CODE, D.ZIPCODE, E.GENDER_NAME,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'TGL_IURAN') TGL_IURAN,
							F_SIS_GET_VARIABEL_ANGGOTA(A.ID_ANGGOTA, 'PLAFOND') PLAFOND,
							(SELECT TO_CHAR(MIN(X.TGL_STATUS),'DD/MM/YYYY') TGL_STATUS FROM SIS_ANGGOTA_HISTORY X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.KODE_STATUS_ANGGOTA = 'ST001') TGL_DAFTAR,
							H.NO_KARTU, H.ID_NUM, H.PIN, I.KD_JENIS_KARTU, I.NAMA_JENIS_KARTU, TO_CHAR(H.TGL_AKTIF,'DD/MM/YYYY') TGL_AKTIF, TO_CHAR(H.TGL_EXPIRE,'DD/MM/YYYY') TGL_EXPIRE, NVL(H.ST_PRINT, 'N') ST_PRINT, TO_CHAR(H.TGL_PRINT,'DD/MM/YYYY') TGL_PRINT, NVL(H.ST_AMBIL, 'N') ST_AMBIL, TO_CHAR(H.TGL_AMBIL,'DD/MM/YYYY') TGL_AMBIL 
					FROM	SIS_ANGGOTA A 
							LEFT JOIN SW_PERSON B ON A.PERSON_ID = B.PERSON_ID 
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA C ON A.KD_JENIS_ANGGOTA = C.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERSON_ADDRESS D ON B.PERSON_ID = D.PERSON_ID  AND D.ADDRESS_TYPE_CODE = 'ADR01' 
							LEFT JOIN SW_GENDER E ON B.GENDER_ID = E.GENDER_ID
							LEFT JOIN KEU_BANK F ON A.KD_BANK = F.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON A.ID_PERUSAHAAN = G.ID_PERUSAHAAN 
							JOIN SIS_ANGGOTA_KARTU H ON A.ID_ANGGOTA = H.ID_ANGGOTA 
							JOIN SIS_SETUP_JENIS_KARTU I ON H.KD_JENIS_KARTU = I.KD_JENIS_KARTU
					WHERE	H.ID_KARTU = '".$as_idkartu."'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			$this->db->delete('sis_anggota_kartu', $arr_rule);
			
			$this->AuditTrail(	'sis_anggota_kartu',	// Table
								'Delete',				// Action
								json_encode($arr_rule)	// Primary Key
								);
			return '';
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
}
?>